<?php
namespace App\Model;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Model\SystemConfig;

class Xtqj extends BaseModel
{
    protected $connection = 'api';
    protected $giftTable = 'gift_library';
    protected $userTable = 'user';
    protected $jdStream_table = 'finance_jd_stream';
    protected $diamondStream_table = 'finance_diamond_stream';

    protected $activityConn = 'activity';
    protected $xtqj_game_table = 'xtqj_game';
    protected $xtqj_offer_table = 'xtqj_offer';
    protected $xtqj_pool_table = 'xtqj_pool';
    protected $xtqj_skill_table = 'xtqj_skill';
    protected $xtqj_white_table = 'xtqj_white';
    protected $xtqj_white_config_table = 'xtqj_white_config';
    protected $xtqj_white_user_table = 'xtqj_white_user';
    protected $xtqj_order_table = 'xtqj_order';
    protected $xtqj_order_draw_table = 'xtqj_order_draw';
    protected $xtqj_order_draw_log_table = 'xtqj_order_draw_log';
    protected $xtqj_offer_open_log_table = 'xtqj_offer_open_log';
    protected $xtqj_offer_finish_log_table = 'xtqj_offer_finish_log';
    protected $xtqj_notice_table = 'xtqj_notice';

    //西天取经游戏配置修改
    public function gameEdit($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/gameEdit', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经游戏开启/关闭
    public function gameStatus($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/gameStatus', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经奖池修改
    public function pollEdit($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/pollEdit', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经技能配置新增
    public function skillAdd($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/skillAdd', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经技能配置修改
    public function skillEdit($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/skillEdit', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经技能配置删除
    public function skillDelete($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/skillDelete', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经悬赏配置新增
    public function offerAdd($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/offerAdd', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经悬赏配置修改
    public function offerEdit($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/offerEdit', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经悬赏配置删除
    public function offerDelete($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/offerDelete', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经白名单新增
    public function whiteAdd($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/whiteAdd', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经白名单修改
    public function whiteEdit($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/whiteEdit', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经白名单删除
    public function whiteDelete($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/whiteDelete', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经白名单配置新增
    public function whiteConfigAdd($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/whiteConfigAdd', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经白名单配置删除
    public function whiteConfigDelete($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/whiteConfigDelete', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经白名单用户新增
    public function whiteUserAdd($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/whiteUserAdd', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经白名单用户删除
    public function whiteUserDelete($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/whiteUserDelete', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //用户战神榜生成
    public function rankdrawRepair($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/rankdrawRepair', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //主播战力榜生成
    public function rankUserRepair($data){
        $res = curl_request(getKkaudioActivityUrl() . '/inner/xtqj/rankUserRepair', $data, 'json', 'POST');
        if(isset($res['code']) && $res['code']==0) {
            return 1;
        }else{
            return [
                'code' => 50000,
                'msg' => empty($res['msg']) ? '失败' : $res['msg'],
            ];
        }
    }

    //西天取经游戏列表
    public function getGameList()
    {
        $list = DB::connection($this->activityConn)->table($this->xtqj_game_table)->get();
        $list = objToArray($list);
        
        return $list;
    }

    //西天取经用户奖池配置列表
    public function getPoolList(Request $request)
    {
        $query = DB::connection($this->activityConn)->table($this->xtqj_pool_table);
        if(!empty($request['game_id'])) {
            $query = $query->where('game_id', $request['game_id']);
        }
        if(!empty($request['item_type'])) {
            $query = $query->where('item_type', $request['item_type']);
        }

        $list = $query->get();
        $list = objToArray($list);
        if(empty($list)) {
            return [];
        }
        $giftIds = array_column($list, 'gift_id');
        $giftList = DB::connection($this->connection)->table($this->giftTable)->whereIn('gift_id', $giftIds)->get();
        $giftList = objToArray($giftList);
        $giftList = array_column($giftList, null, 'gift_id');

        $gameList = $this->getGameList();
        $gameList = array_column($gameList, null, 'game_id');
        foreach($list as $key=>$val) {
            $list[$key]['gameName'] = empty($gameList[$val['game_id']]['game_name']) ? '' : $gameList[$val['game_id']]['game_name'];
            $list[$key]['giftName'] = empty($giftList[$val['gift_id']]['name']) ? '' : $giftList[$val['gift_id']]['name'];
        }

        return $list;
    }

    //西天取经技能配置列表
    public function getSkillList(Request $request)
    {
        $gameList = $this->getGameList();
        $gameList = array_column($gameList, null, 'game_id');

        $query = DB::connection($this->activityConn)->table($this->xtqj_skill_table)->where('is_delete', 0);
        if(!empty($request['item_id'])) {
            $query = $query->where('item_id', $request['item_id']);
        }
        $list = $query->get();
        $list = objToArray($list);
        if(empty($list)) {
            return [];
        }
        $itemIds = array_column($list, 'item_id');
        $itemList = DB::connection($this->activityConn)->table($this->xtqj_pool_table)->whereIn('id', $itemIds)->get();
        $itemList = array_column(objToArray($itemList), null, 'id');

        foreach($list as $key=>$val) {
            $val['item_name'] = empty($itemList[$val['item_id']]['item_name']) ? '' : $itemList[$val['item_id']]['item_name'];
            $val['game_id'] = empty($itemList[$val['item_id']]['game_id']) ? '' : $itemList[$val['item_id']]['game_id'];
            $val['game_name'] = empty($gameList[$val['game_id']]['game_name']) ? '' : $gameList[$val['game_id']]['game_name'];
            $val['skill_reward'] = json_decode($val['skill_reward'], true);

            $list[$key] = $val;
        }

        return $list;
    }

    //西天取经悬赏配置列表
    public function getOfferList(Request $request)
    {
        $gameList = $this->getGameList();
        $gameList = array_column($gameList, null, 'game_id');

        $query = DB::connection($this->activityConn)->table($this->xtqj_offer_table)->where('is_delete', 0);
        if(!empty($request['game_id'])) {
            $query = $query->where('game_id', $request['game_id']);
        }

        $list = $query->get();
        $list = objToArray($list);
        if(empty($list)) {
            return [];
        }
        $itemIds = [];
        $giftIds = [];
        foreach($list as $key=>$val) {
            $val['offer_draw'] = json_decode($val['offer_draw'], true);
            $itemIds = array_merge($itemIds, $val['offer_draw']);
            $val['offer_reward'] = json_decode($val['offer_reward'], true);
            $giftIds = array_merge($giftIds, array_column($val['offer_reward'], 'gift_id'));
            $list[$key] = $val;
        }
        $itemList = [];
        $itemList = DB::connection($this->activityConn)->table($this->xtqj_pool_table)->whereIn('id', $itemIds)->get();
        $itemList = array_column(objToArray($itemList), null, 'id');

        $giftList = DB::connection($this->connection)->table($this->giftTable)->whereIn('gift_id', $giftIds)->get();
        $giftList = array_column(objToArray($giftList), null, 'gift_id');

        foreach($list as $key=>$val) {
            $val['gameName'] = empty($gameList[$val['game_id']]['game_name']) ? '' : $gameList[$val['game_id']]['game_name'];
            foreach($val['offer_draw'] as $k2=>$itemId) {
                $val['offer_draw'][$k2] = [
                    'item_id' => $itemId,
                    'item_name' => empty($itemList[$itemId]['item_name']) ? '' : $itemList[$itemId]['item_name'],
                ];
            }
            foreach($val['offer_reward'] as $k2=>$v2) {
                $val['offer_reward'][$k2]['gift_name'] = empty($giftList[$v2['gift_id']]['name']) ? '' : $giftList[$v2['gift_id']]['name'];
            }

            $list[$key] = $val;
        }

        return $list;
    }

    //西天取经白名单列表
    public function getWhiteList(Request $request)
    {
        $gameList = $this->getGameList();
        $gameList = array_column($gameList, null, 'game_id');

        $query = DB::connection($this->activityConn)->table($this->xtqj_white_table)->where('is_delete', 0);
        if(!empty($request['game_id'])) {
            $query = $query->where('game_id', $request['game_id']);
        }

        $list = $query->get();
        $list = objToArray($list);
        if(empty($list)) {
            return [];
        }

        foreach($list as $key=>$val) {
            $val['gameName'] = empty($gameList[$val['game_id']]['game_name']) ? '' : $gameList[$val['game_id']]['game_name'];
            //白名单用户
            $val['white_user'] = [];
            $whiteUserList = DB::connection($this->activityConn)->table($this->xtqj_white_user_table)->where('white_id', $val['id'])->where('is_delete', 0)->get();
            $whiteUserList = objToArray($whiteUserList);
            if(!empty($whiteUserList)) {
                $userIds = array_column($whiteUserList, 'user_id');
                $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id','nickname','uuid')->get();
                $userList = array_column(objToArray($userList), null, 'user_id');
                foreach($whiteUserList as $v2) {
                    $val['white_user'][] = [
                        'user_id' => $v2['user_id'],
                        'nickname' => empty($userList[$v2['user_id']]['nickname']) ? '' : $userList[$v2['user_id']]['nickname'],
                        'uuid' => empty($userList[$v2['user_id']]['uuid']) ? '' : $userList[$v2['user_id']]['uuid'],
                    ];
                }
            }

            $list[$key] = $val;
        }

        return $list;
    }

    //西天取经白名单配置列表
    public function getWhiteConfigList(Request $request)
    {
        $gameList = $this->getGameList();
        $gameList = array_column($gameList, null, 'game_id');

        $query = DB::connection($this->activityConn)->table($this->xtqj_white_config_table.' as a')
            ->leftJoin($this->xtqj_white_table.' as b' ,'a.white_id','=','b.id')
            ->select("a.*", "b.game_id")
            ->where('a.is_delete', 0);
        if(!empty($request['white_id'])) {
            $query = $query->where('a.white_id', $request['white_id']);
        }

        $list = $query->get();
        $list = objToArray($list);
        if(empty($list)) {
            return [];
        }

        $itemIds = [];
        foreach($list as $key=>$val) {
            $val['white_reward'] = json_decode($val['white_reward'], true);
            $itemIds = array_merge($itemIds, array_column($val['white_reward'], 'item_id'));
            $list[$key] = $val;
        }
        $itemList = DB::connection($this->activityConn)->table($this->xtqj_pool_table)->whereIn('id', $itemIds)->get();
        $itemList = array_column(objToArray($itemList), null, 'id');

        foreach($list as $key=>$val) {
            $val['gameName'] = empty($gameList[$val['game_id']]['game_name']) ? '' : $gameList[$val['game_id']]['game_name'];
            foreach($val['white_reward'] as $k2=>$v2) {
                $val['white_reward'][$k2]['item_name'] = empty($itemList[$v2['item_id']]['item_name']) ? '' : $itemList[$v2['item_id']]['item_name'];
            }

            $list[$key] = $val;
        }

        return $list;
    }

    //西天取经白名单用户列表
    public function getWhiteUserList(Request $request)
    {
        $gameList = $this->getGameList();
        $gameList = array_column($gameList, null, 'game_id');

        $query = DB::connection($this->activityConn)->table($this->xtqj_white_user_table)
            ->where('is_delete', 0)
            ->where('white_id', $request['white_id']);

        $list = $query->get();
        $list = objToArray($list);
        if(empty($list)) {
            return [];
        }

        $userIds = array_column($list, 'user_id');
        $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->get();
        $userList = array_column(objToArray($userList), null, 'user_id');

        foreach($list as $key=>$val) {
            $val['nickname'] = empty($userList[$val['user_id']]['nickname']) ? '' : $userList[$val['user_id']]['nickname'];
            $val['uuid'] = empty($userList[$val['user_id']]['uuid']) ? '' : $userList[$val['user_id']]['uuid'];

            $list[$key] = $val;
        }

        return $list;
    }

    //西天取经统计数据概述
    public function statSearchAll(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $game_id = $request->input('game_id');

        $query1 = DB::connection($this->activityConn)->table($this->xtqj_order_table);
        if(!empty($date_st)) {
            $query1 = $query1->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
        }
        if(!empty($date_ed)) {
            $query1 = $query1->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
        }
        if(!empty($game_id)) {
            $query1 = $query1->where('game_id', $game_id);
        }
        $res1 = $query1->selectRaw("count(DISTINCT user_id) as userNum, sum(price_total) as price_total, sum(reward_diamand_num) as reward_diamand_num")
                        ->first();
        $res1 = objToArray($res1);


        $query2 = DB::connection($this->activityConn)->table($this->xtqj_offer_finish_log_table);
        if(!empty($date_st)) {
            $query2 = $query2->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
        }
        if(!empty($date_ed)) {
            $query2 = $query2->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
        }
        if(!empty($game_id)) {
            $query2 = $query2->where('game_id', $game_id);
        }
        $res2 = $query2->selectRaw("sum(reward_diamand_num) as reward_diamand_num")
                        ->first();
        $res2 = objToArray($res2);

        $result = [];
        //人数
        $result['userNum'] = (int) $res1['userNum'];
        //消耗金豆
        $result['payNum'] = (int) $res1['price_total'];
        //抽奖产出金豆
        $result['getNum'] = (int) $res1['reward_diamand_num'];
        //悬赏产出金豆
        $result['offerNum'] = (int) $res2['reward_diamand_num'];
        //利润金豆
        $result['profitNum'] = $result['payNum'] - $result['getNum'] - $result['offerNum'];
        //利润率
        $result['profiRate'] = empty($result['payNum']) ? 0 : round(($result['profitNum']/$result['payNum'])*100, 2);

        return $result;
    }

    //西天取经悬赏整体数据统计
    public function statOfferAll(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $game_id = $request->input('game_id');

        if(empty($game_id)) {
            $gameIds = [1,2];
        }else{
            $gameIds = [$game_id];
        }

        $gameList = DB::connection($this->activityConn)->table($this->xtqj_game_table)->get();
        $gameList = objToArray($gameList);
        $gameList = array_column($gameList, null, 'game_id');

        $list = [];
        foreach($gameIds as $game_id) {
            $query1 = DB::connection($this->activityConn)->table($this->xtqj_offer_open_log_table);
            if(!empty($date_st)) {
                $query1 = $query1->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
            }
            if(!empty($date_ed)) {
                $query1 = $query1->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
            }
            if(!empty($game_id)) {
                $query1 = $query1->where('game_id', $game_id);
            }
            $res1 = $query1->selectRaw("count(1) as openNum")
                            ->first();
            $res1 = objToArray($res1);


            $query2 = DB::connection($this->activityConn)->table($this->xtqj_offer_finish_log_table);
            if(!empty($date_st)) {
                $query2 = $query2->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
            }
            if(!empty($date_ed)) {
                $query2 = $query2->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
            }
            if(!empty($game_id)) {
                $query2 = $query2->where('game_id', $game_id);
            }
            $res2 = $query2->selectRaw("count(1) as finishNum, count(DISTINCT user_id) as userNum, sum(reward_diamand_num) as reward_diamand_num")
                            ->first();
            $res2 = objToArray($res2);


            $query3 = DB::connection($this->activityConn)->table($this->xtqj_order_draw_table);
            if(!empty($date_st)) {
                $query3 = $query3->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
            }
            if(!empty($date_ed)) {
                $query3 = $query3->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
            }
            if(!empty($game_id)) {
                $query3 = $query3->where('game_id', $game_id);
            }
            $res3 = $query3->selectRaw("sum(price) as payNum")
                            ->where('if_offer', 1)
                            ->first();
            $res3 = objToArray($res3);

            $item = [];
            $item['game_id'] = $game_id;
            $item['game_name'] = empty($gameList[$game_id]['game_name']) ? '' : $gameList[$game_id]['game_name'];
            //开启次数
            $item['openNum'] = (int) $res1['openNum'];
            //完成次数
            $item['finishNum'] = (int) $res2['finishNum'];
            //完成人次
            $item['userNum'] = (int) $res2['userNum'];
            //悬赏产出金豆
            $item['offerNum'] = (int) $res2['reward_diamand_num'];
            //开启期间消耗金豆数量
            $item['payNum'] = (int) $res3['payNum'];
            $list[] = $item;
        }

        return ['list'=>$list];
    }

    //西天取经各悬赏ID数据
    public function statOfferList(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $game_id = $request->input('game_id');

        if(empty($game_id)) {
            $gameIds = [1,2];
        }else{
            $gameIds = [$game_id];
        }

        $gameList = DB::connection($this->activityConn)->table($this->xtqj_game_table)->get();
        $gameList = objToArray($gameList);
        $gameList = array_column($gameList, null, 'game_id');

        $list = [];
        foreach($gameIds as $game_id) {
            $query1 = DB::connection($this->activityConn)->table($this->xtqj_offer_open_log_table);
            if(!empty($date_st)) {
                $query1 = $query1->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
            }
            if(!empty($date_ed)) {
                $query1 = $query1->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
            }
            if(!empty($game_id)) {
                $query1 = $query1->where('game_id', $game_id);
            }
            $res1 = $query1->selectRaw("count(1) as openNum,offer_id")
                            ->groupBy('offer_id')
                            ->get();
            $res1 = objToArray($res1);
            $res1 = array_column($res1, null, 'offer_id');

            $query2 = DB::connection($this->activityConn)->table($this->xtqj_offer_finish_log_table);
            if(!empty($date_st)) {
                $query2 = $query2->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
            }
            if(!empty($date_ed)) {
                $query2 = $query2->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
            }
            if(!empty($game_id)) {
                $query2 = $query2->where('game_id', $game_id);
            }
            $res2 = $query2->selectRaw("count(1) as finishNum, count(DISTINCT user_id) as userNum, sum(reward_diamand_num) as reward_diamand_num, offer_id")
                            ->groupBy('offer_id')
                            ->get();
            $res2 = objToArray($res2);
            $res2 = array_column($res2, null, 'offer_id');

            $query3 = DB::connection($this->activityConn)->table($this->xtqj_order_draw_table);
            if(!empty($date_st)) {
                $query3 = $query3->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
            }
            if(!empty($date_ed)) {
                $query3 = $query3->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
            }
            if(!empty($game_id)) {
                $query3 = $query3->where('game_id', $game_id);
            }
            $res3 = $query3->selectRaw("sum(price) as payNum, offer_id")
                            ->where('if_offer', 1)
                            ->groupBy('offer_id')
                            ->get();
            $res3 = objToArray($res3);
            $res3 = array_column($res3, null, 'offer_id');

            $offerList = DB::connection($this->activityConn)->table($this->xtqj_offer_table)->where('game_id', $game_id)->get();
            $offerList = objToArray($offerList);
            foreach($offerList as $val) {
                $item = [];
                $item['game_id'] = $game_id;
                $item['game_name'] = empty($gameList[$game_id]['game_name']) ? '' : $gameList[$game_id]['game_name'];
                //悬赏ID
                $item['offer_id'] = $val['id'];
                //开启次数
                $item['openNum'] = empty($res1[$item['offer_id']]['openNum']) ? 0 : $res1[$item['offer_id']]['openNum'];
                //完成次数
                $item['finishNum'] = empty($res2[$item['offer_id']]['finishNum']) ? 0 : $res2[$item['offer_id']]['finishNum'];
                //完成人次
                $item['userNum'] = empty($res2[$item['offer_id']]['userNum']) ? 0 : $res2[$item['offer_id']]['userNum'];
                //悬赏产出金豆
                $item['offerNum'] = empty($res2[$item['offer_id']]['reward_diamand_num']) ? 0 : $res2[$item['offer_id']]['reward_diamand_num'];
                //开启期间消耗金豆数量
                $item['payNum'] = empty($res3[$item['offer_id']]['payNum']) ? 0 : $res3[$item['offer_id']]['payNum'];
                $list[] = $item;
            }
        }

        return ['list'=>$list];
    }

    //西天取经各期悬赏数据
    public function statOfferOpenList(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $game_id = $request->input('game_id');
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);

        $gameList = DB::connection($this->activityConn)->table($this->xtqj_game_table)->get();
        $gameList = objToArray($gameList);
        $gameList = array_column($gameList, null, 'game_id');

        $query = DB::connection($this->activityConn)->table($this->xtqj_offer_open_log_table);
        if(!empty($date_st)) {
            $query = $query->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
        }
        if(!empty($date_ed)) {
            $query = $query->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
        }
        if(!empty($game_id)) {
            $query = $query->where('game_id', $game_id);
        }
        $offerOpenList = $query->orderBy('time_st', 'desc')->paginate($limit, ['*'], 'page', $page);
        $offerOpenList = objToArray($offerOpenList);
        foreach($offerOpenList['data'] as $key=>$val) {
            $res2 = DB::connection($this->activityConn)->table($this->xtqj_offer_finish_log_table)
                ->where('offer_open_id', $val['id'])
                ->selectRaw("count(1) as finishNum")
                ->first();
            $res2 = objToArray($res2);

            $res3 = DB::connection($this->activityConn)->table($this->xtqj_order_draw_table)
                ->where('if_offer', 1)
                ->where('offer_open_id', $val['id'])
                ->selectRaw("sum(price) as payNum, count(DISTINCT user_id) as userNum")
                ->first();
            $res3 = objToArray($res3);

            $item = [];
            $item['game_id'] = $val['game_id'];
            $item['game_name'] = empty($gameList[$item['game_id']]['game_name']) ? '' : $gameList[$item['game_id']]['game_name'];
            $item['offer_open_id'] = $val['id'];
            //悬赏ID
            $item['offer_id'] = $val['offer_id'];
            //开始时间
            $item['time_st'] = $val['time_st'];
            //结束时间
            $item['time_ed'] = $val['time_ed'];
            //参与人数
            $item['userNum'] = (int) $res3['userNum'];
            //完成人数
            $item['finishNum'] = (int) $res2['finishNum'];
            //开启期间消耗金豆数量
            $item['payNum'] = (int) $res3['payNum'];

            $offerOpenList['data'][$key] = $item;
        }

        return $offerOpenList;
    }

    //西天取经悬赏记录各期明细
    public function statOfferFinsihList(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $game_id = $request->input('game_id');
        $user_id = $request->input('user_id');
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);

        $gameList = DB::connection($this->activityConn)->table($this->xtqj_game_table)->get();
        $gameList = array_column(objToArray($gameList), null, 'game_id');

        $itemList = DB::connection($this->activityConn)->table($this->xtqj_pool_table)->get();
        $itemList = array_column(objToArray($itemList), null, 'id');

        $query = DB::connection($this->activityConn)->table($this->xtqj_offer_finish_log_table.' as a');
        $query = $query->leftJoin($this->xtqj_offer_open_log_table.' as b', 'a.offer_open_id', '=', 'b.id');
        if(!empty($date_st)) {
            $query = $query->where('a.created_at', '>=', date("Y-m-d H:i:s", $date_st));
        }
        if(!empty($date_ed)) {
            $query = $query->where('a.created_at', '<=', date("Y-m-d H:i:s", $date_ed));
        }
        if(!empty($game_id)) {
            $query = $query->where('a.game_id', $game_id);
        }
        if(!empty($user_id)) {
            $query = $query->where('a.user_id', $user_id);
        }
        $offerOpenList = $query->orderBy('id', 'desc')->paginate($limit, ['a.*','b.offer_draw','b.offer_reward','b.time_st','b.time_ed'], 'page', $page);
        $offerOpenList = objToArray($offerOpenList);

        if(!empty($offerOpenList['data'])) {
            $userIds = array_column($offerOpenList['data'], 'user_id');
            $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id','nickname','uuid')->get();
            $userList = array_column(objToArray($userList), null, 'user_id');

            $giftIds = [];
            foreach($offerOpenList['data'] as $key=>$val) {
                $offer_reward = json_decode($val['offer_reward'], true);
                $giftIds = array_merge($giftIds, array_column($offer_reward, 'gift_id'));
            }
            if(!empty($giftIds)) {
                $giftList = DB::connection($this->connection)->table($this->giftTable)->whereIn('gift_id', $giftIds)->get();
                $giftList = objToArray($giftList);
                $giftList = array_column($giftList, null, 'gift_id');
            }
        }

        foreach($offerOpenList['data'] as $key=>$val) {
            $offer_draw = json_decode($val['offer_draw'], true);
            $offerDraw = [];
            foreach($offer_draw as $v2) {
                $offerDraw[] = empty($itemList[$v2]['item_name']) ? '' : $itemList[$v2]['item_name'];
            }

            $offerReward = json_decode($val['offer_reward'], true);
            foreach($offerReward as $k2=>$v2) {
                $offerReward[$k2]['gift_name'] = empty($giftList[$v2['gift_id']]['name']) ? '' : $giftList[$v2['gift_id']]['name'];
            }

            $item = [];
            $item['finish_id'] = $val['id'];
            $item['game_id'] = $val['game_id'];
            $item['game_name'] = empty($gameList[$item['game_id']]['game_name']) ? '' : $gameList[$item['game_id']]['game_name'];
            //用户
            $item['user_id'] = $val['user_id'];
            $item['nickname'] = empty($userList[$item['user_id']]['nickname']) ? '' : $userList[$item['user_id']]['nickname'];
            $item['uuid'] = empty($userList[$item['user_id']]['uuid']) ? '' : $userList[$item['user_id']]['uuid'];
            //悬赏ID
            $item['offer_id'] = $val['offer_id'];
            //悬赏开启ID
            $item['offer_open_id'] = $val['offer_open_id'];
            //开始时间
            $item['time_st'] = $val['time_st'];
            //结束时间
            $item['time_ed'] = $val['time_ed'];
            //完成时间
            $item['finish_st'] = strtotime($val['created_at']);
            //领取时间
            $item['receive_st'] = empty($val['status']) ? 0 : strtotime($val['updated_at']);
            //悬赏人物
            $item['offer_draw'] = $offerDraw;
            //奖励内容
            $item['offer_reward'] = $offerReward;
            //奖励总价值
            $item['reward_diamand_num'] = $val['reward_diamand_num'];

            $offerOpenList['data'][$key] = $item;
        }

        return $offerOpenList;
    }

    //西天取经用户参与排行
    public function statUserOrder(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $game_id = $request->input('game_id');

        $query1 = DB::connection($this->activityConn)->table($this->xtqj_order_table);
        if(!empty($date_st)) {
            $query1 = $query1->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
        }
        if(!empty($date_ed)) {
            $query1 = $query1->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
        }
        if(!empty($game_id)) {
            $query1 = $query1->where('game_id', $game_id);
        }
        $res1 = $query1->selectRaw("sum(price_total) as price_total, sum(reward_diamand_num) as reward_diamand_num, user_id")
                    ->groupBy('user_id')
                    ->get();
        $res1 = objToArray($res1);
        $res1 = array_column($res1, null, 'user_id');

        $query2 = DB::connection($this->activityConn)->table($this->xtqj_offer_finish_log_table);
        if(!empty($date_st)) {
            $query2 = $query2->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
        }
        if(!empty($date_ed)) {
            $query2 = $query2->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
        }
        if(!empty($game_id)) {
            $query2 = $query2->where('game_id', $game_id);
        }
        $res2 = $query2->selectRaw("sum(reward_diamand_num) as reward_diamand_num, user_id")
                    ->groupBy('user_id')
                    ->get();
        $res2 = objToArray($res2);
        $res2 = array_column($res2, null, 'user_id');

        $userIds = array_unique(array_merge(array_column($res1,'user_id'),array_column($res2,'user_id')));
        $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id','nickname','uuid')->get();
        $userList = array_column(objToArray($userList), null, 'user_id');

        $list = [];
        foreach($userIds as $userId) {
            if(isset($list[$userId])) {
                continue;
            }

            $item = [];
            //用户
            $item['user_id'] = $userId;
            $item['nickname'] = empty($userList[$userId]['nickname']) ? '' : $userList[$userId]['nickname'];
            $item['uuid'] = empty($userList[$userId]['uuid']) ? '' : $userList[$userId]['uuid'];
            //消耗金豆数量
            $item['payNum'] = empty($res1[$userId]['price_total']) ? 0 : (int) $res1[$userId]['price_total'];
            //产出金豆数量
            $item['getNum'] = empty($res1[$userId]['reward_diamand_num']) ? 0 : (int) $res1[$userId]['reward_diamand_num'];
            //悬赏产出金豆数量
            $item['offerNum'] = empty($res2[$userId]['reward_diamand_num']) ? 0 : (int) $res2[$userId]['reward_diamand_num'];
            //利润金豆数量
            $item['profitNum'] = $item['getNum'] + $item['offerNum'] - $item['payNum'];
            $list[] = $item;
        }

        return $list;
    }

    //西天取经指定用户挑战记录
    public function userDrawList(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $game_id = $request->input('game_id');
        $user_id = $request->input('user_id');
        $limit = $request->input('limit', 10);
        $page = $request->input('page', 1);

        $gameList = DB::connection($this->activityConn)->table($this->xtqj_game_table)->get();
        $gameList = array_column(objToArray($gameList), null, 'game_id');

        $itemList = DB::connection($this->activityConn)->table($this->xtqj_pool_table)->get();
        $itemList = array_column(objToArray($itemList), null, 'id');

        $query = DB::connection($this->activityConn)->table($this->xtqj_order_table);
        if(!empty($date_st)) {
            $query = $query->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
        }
        if(!empty($date_ed)) {
            $query = $query->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
        }
        if(!empty($game_id)) {
            $query = $query->where('game_id', $game_id);
        }
        if(!empty($user_id)) {
            $query = $query->where('user_id', $user_id);
        }
        $result = $query->orderBy('id', 'desc')->paginate($limit, ['*'], 'page', $page);
        $result = objToArray($result);

        if(!empty($result['data'])) {
            $userIds = array_column($result['data'], 'user_id');
            $userList = DB::connection($this->connection)->table($this->userTable)->whereIn('user_id', $userIds)->select('user_id','nickname','uuid')->get();
            $userList = array_column(objToArray($userList), null, 'user_id');
        }

        $list = [];
        foreach($result['data'] as $val) {
            //抽奖记录
            $drawList = DB::connection($this->activityConn)->table($this->xtqj_order_draw_table)->where('order_id', $val['id'])->get();
            $drawList = objToArray($drawList);
            //抽奖记录明细
            $drawIds = array_column($drawList, 'id');
            $drawLogList = DB::connection($this->activityConn)->table($this->xtqj_order_draw_log_table)->whereIn('draw_id', $drawIds)->get();
            $drawLogList = objToArray($drawLogList);
            //礼物
            $giftIds = array_column($drawLogList, 'gift_id');
            $giftList = DB::connection($this->connection)->table($this->giftTable)->whereIn('gift_id', $giftIds)->get();
            $giftList = objToArray($giftList);
            $giftList = array_column($giftList, null, 'gift_id');

            $orderInfo = [];
            //订单ID
            $orderInfo['order_id'] = $val['id'];
            //游戏
            $orderInfo['game_id'] = $val['game_id'];
            $orderInfo['game_name'] = empty($gameList[$orderInfo['game_id']]['game_name']) ? '' : $gameList[$orderInfo['game_id']]['game_name'];
            //用户
            $orderInfo['user_id'] = $val['user_id'];
            $orderInfo['nickname'] = empty($userList[$orderInfo['user_id']]['nickname']) ? '' : $userList[$orderInfo['user_id']]['nickname'];
            $orderInfo['uuid'] = empty($userList[$orderInfo['user_id']]['uuid']) ? '' : $userList[$orderInfo['user_id']]['uuid'];
            //总挑战次数
            $orderInfo['drawNum'] = (int) $val['num'];
            //奖励价值钻石数
            $orderInfo['reward_diamand_num'] = (int) $val['reward_diamand_num'];
            //时间
            $orderInfo['created_at'] = strtotime($val['created_at']);
            //抽奖记录
            $orderInfo['logs'] = [];
            $i = 1;
            foreach($drawList as $v2) {
                $itemInfo = $itemList[$v2['item_id']];
                //抽奖记录明细
                $rewardGifts = [];
                foreach($drawLogList as $v3) {
                    if($v3['draw_id'] == $v2['id']) {
                        $rewardGifts[] = empty($giftList[$v3['gift_id']]['name']) ? '' : $giftList[$v3['gift_id']]['name'];
                    }
                }

                $item = [];
                //当前挑战序号
                $item['i'] = $i;
                //类型 1礼物 2技能
                $item['itemType'] = (int) $v2['item_type'];
                //抽中人物
                $item['itemName'] = $itemInfo['item_name'];
                //礼物奖励
                $item['rewardGifts'] = $rewardGifts;
                //礼物总价值
                $item['reward_diamand_num'] = (int) $v2['reward_diamand_num'];

                $orderInfo['logs'][] = $item;
                $i++;
            }
            $list[] = $orderInfo;
        }
        $result['data'] = $list;

        return $result;
    }

    //西天取经奖励产出查询
    public function statPoolItem(Request $request)
    {
        $date_st = $request->input('date_st');
        $date_ed = $request->input('date_ed');
        $game_id = $request->input('game_id');

        $gameList = DB::connection($this->activityConn)->table($this->xtqj_game_table)->get();
        $gameList = array_column(objToArray($gameList), null, 'game_id');

        $itemList = DB::connection($this->activityConn)->table($this->xtqj_pool_table)->get();
        $itemList = array_column(objToArray($itemList), null, 'id');

        $query1 = DB::connection($this->activityConn)->table($this->xtqj_order_draw_table);
        if(!empty($date_st)) {
            $query1 = $query1->where('created_at', '>=', date("Y-m-d H:i:s", $date_st));
        }
        if(!empty($date_ed)) {
            $query1 = $query1->where('created_at', '<=', date("Y-m-d H:i:s", $date_ed));
        }
        if(!empty($game_id)) {
            $query1 = $query1->where('game_id', $game_id);
        }
        $data = $query1->selectRaw("count(1) as itemNum, sum(reward_diamand_num) as reward_diamand_num, item_id, game_id")
                    ->groupBy('item_id','game_id')
                    ->get();
        $data = objToArray($data);

        $list = [];
        $count = array_sum(array_column($data, 'itemNum'));
        foreach($data as $val) {
            $item = [];
            //游戏
            $item['game_id'] = $val['game_id'];
            $item['game_name'] = empty($gameList[$item['game_id']]['game_name']) ? '' : $gameList[$item['game_id']]['game_name'];
            //人物
            $item['itemId'] = $val['item_id'];
            $item['itemName'] = empty($itemList[$item['itemId']]['item_name']) ? '' : $itemList[$item['itemId']]['item_name'];
            //数量
            $item['itemNum'] = (int) $val['itemNum'];
            //当前概率
            $item['rate'] = round(($val['itemNum']/$count)*100, 2);
            //产出价值
            $item['diamandNum'] = (int) $val['reward_diamand_num'];

            $list[] = $item;
        }
        $orderBy1 = array_column($list, 'game_id');
        $orderBy2 = array_column($list, 'itemId');
        array_multisort($orderBy1, SORT_DESC, $orderBy2, SORT_ASC, $list);

        $result['data'] = $list;

        return $result;
    }


}
